package jsyf.oa.dao.intentCustomerFromBusiness;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import jsyf.oa.entity.report.intentCustomer.ReportCustomerEntity;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

/**
 * Created by 92852 on 2018/9/8.
 */
@Repository
public class IntentCustomerFromBusinessDao extends BaseDao {

    public IntentCustomerFromBusinessDao() {
        super.iBaseEntity = new ReportCustomerEntity();
    }

    public List<ReportCustomerEntity> getEntityListByCondition(String condition, Object[] params) {
        List<JSONObject> jsonList = getJSONListByCondition(condition, params);
        List<ReportCustomerEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            ReportCustomerEntity entity = (ReportCustomerEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, ReportCustomerEntity.class);
    }

    public List<JSONObject> getForGroupIntentCustomerList(String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_company.ID AS companyId,\n" +
                "t_sys_company.`NAME` AS companyName,\n" +
                "COUNT(t_wrt_customer.ID) AS num,\n" +
                "COUNT(case when (t_wrt_customer.BUY_DATE='一周内'  OR t_wrt_customer.BUY_DATE='一周' )then t_wrt_customer.BUY_DATE end ) AS weekNum,\n" +
                "COUNT(case when (t_wrt_customer.BUY_DATE='一月内' OR t_wrt_customer.BUY_DATE='本月') then t_wrt_customer.BUY_DATE end ) AS MonNum,\n" +
                "COUNT(case when t_wrt_customer.BUY_DATE='一月以上' then t_wrt_customer.BUY_DATE end ) AS MoreNum\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID AND t_sys_user.JOB_STATE<>1\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID AND t_sys_role.DEL_FLAG<>1\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID AND t_sys_depart.DEL_FLAG<>1\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID AND t_sys_company.DEL_FLAG<>1\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "WHERE 1=1\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_sys_role_name.`NAME`='区域经理'\n" +
                "AND `t_wrt_customer`.`CREATE_USER_ID` IS NOT NULL\n" +
                "AND `t_wrt_customer`.`NAME` IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in('无','暂无')\n" +
                "AND t_sys_company.ID in(" + companyId + ")\n" +
                "GROUP BY t_sys_company.ID\n" +
                "ORDER BY t_sys_company.SORT_NO\n");
        return super.queryForJsonList(sql.toString());
    }

    /*获取身份标志manageFlag,frontFlag,areaType,*/
    public JSONObject getManageFlagAndFrontFlagAndAreaTypeByUserId(String userId) {
        String sql = "SELECT \n" +
                "t_sys_user.ID as userId,\n" +
                "t_sys_user.`NAME` as userName,\n" +
                "t_sys_role_name.FRONT_FLAG as frontFlag,\n" +
                "t_sys_role_name.AREA_TYPE as areaType,\n" +
                "t_sys_role_name.MANAGE_FLAG as manageFlag\n" +
                " from t_sys_user \n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "WHERE t_sys_user.`ID` = '" + userId + "'";
        JSONObject objUser = super.queryForJsonObject(sql, null);
        return objUser;
    }

    public List<JSONObject> getAllIntentCustomerList(String userId, String condition, String companyId, int requestPage, int pageSize, JSONObject roleFlag) {
         /*查询所有客户*/
        String sqlCustomer = "";
        if (roleFlag.getString("manageFlag").equals("1")) {
            //集团管理员
            sqlCustomer = "SELECT\n" +
                    "\tt_sys_user.ID AS createUserId,\n" +
                    "\tt_sys_user.`NAME` AS createUserName,\n" +
                    "\tt_wrt_customer.`NAME` AS customerName,\n" +
                    "\tt_wrt_customer.`ID` AS customerId,\n" +
                    "\tt_wrt_customer.TEL AS tel,\n" +
                    "\tt_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                    "\tt_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                    "\tt_wrt_customer.PROVINCE_NAME AS provinceName,\n" +
                    "\tt_wrt_customer.CITY_NAME AS cityName,\n" +
                    "\tt_wrt_customer.COUNTY_NAME AS countyName,\n" +
                    "\tconcat_ws(\n" +
                    "\t\t'-',\n" +
                    "\t\t`t_wrt_customer`.`CITY_NAME`,\n" +
                    "\t\t`t_wrt_customer`.`COUNTY_NAME`\n" +
                    "\t) AS `customerlocation`,\n" +
                    "\t`t_wrt_customer`.`BUY_DATE` AS `buyDate`,\n" +
                    "\t`t_wrt_customer`.`BUY_TYPE` AS `buyType`,\n" +
                    "\t`t_wrt_customer`.`INTENTION_MODEL` AS `intentionModel`,\n" +
                    "\t`t_wrt_customer`.`INTENTION_FLAG` AS `intentionFlag`,\n" +
                    "\t`t_wrt_customer`.`THROW_REASON` AS `throwReason`,\n" +
                    "\t`t_wrt_customer`.`INTENTION_TIME` AS `intentionTime`,\n" +
                    "\t`t_wrt_customer`.`OTHER_CONDITION` AS `otherCondition`,\n" +
                    "\t`t_wrt_customer`.`CHANCE` AS `chance`,\n" +
                    "\t`t_wrt_customer`.`BUSINESS_DATE` AS `businessDate`,\n" +
                    "\t`t_wrt_customer`.`WECHAT` AS `wechat`,\n" +
                    "\t`t_wrt_customer`.`BIRTHDAY` AS `birthday`,\n" +
                    "\t`t_wrt_customer`.`EXISTING_DEVICE` AS `existingDevice`\n" +
                    "FROM\n" +
                    "\tt_wrt_customer\n" +
                    "LEFT JOIN t_sys_user ON t_sys_user.ID = t_wrt_customer.CREATE_USER_ID\n" +
                    "AND t_sys_user.JOB_STATE <> 1\n" +
                    "LEFT JOIN t_sys_role ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                    "AND t_sys_role.DEL_FLAG <> 1\n" +
                    "LEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                    "AND t_sys_depart.DEL_FLAG <> 1\n" +
                    "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                    "AND t_sys_company.DEL_FLAG <> 1\n" +
                    "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = t_sys_role.ROLE_NAME_ID\n" +
                    "WHERE 1 = 1\n" +
                    "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                    "AND t_sys_role_name.`NAME` = '区域经理'\n" +
                    "AND `t_wrt_customer`.`CREATE_USER_ID` IS NOT NULL\n" +
                    "AND `t_wrt_customer`.`NAME` IS NOT NULL\n" +
                    "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                    "AND t_wrt_customer.INTENTION_MODEL NOT in ('无', '暂无')\n" +
                    "AND t_sys_company.ID IN (\n"+companyId+")\n" +
                    condition+
                    "GROUP BY\n" +
                    "\tt_sys_user.ID,\n" +
                    "\tt_wrt_customer.`ID`\n" +
                    "ORDER BY\n" +
                    "\tt_sys_company.SORT_NO,t_sys_user.ID,t_wrt_customer.CREATE_TIME DESC";

        } else if (roleFlag.getString("frontFlag").equals("1")) {
            if (roleFlag.getString("areaType").equals("1")) {
                //大区经理
                sqlCustomer = "SELECT\n" +
                        "\tt_sys_user.ID AS createUserId,\n" +
                        "\tt_sys_user.`NAME` AS createUserName,\n" +
                        "\tt_wrt_customer.`NAME` AS customerName,\n" +
                        "\tt_wrt_customer.`ID` AS customerId,\n" +
                        "\tt_wrt_customer.TEL AS tel,\n" +
                        "\tt_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                        "\tt_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                        "\tt_wrt_customer.PROVINCE_NAME AS provinceName,\n" +
                        "\tt_wrt_customer.CITY_NAME AS cityName,\n" +
                        "\tt_wrt_customer.COUNTY_NAME AS countyName,\n" +
                        "\tconcat_ws(\n" +
                        "\t\t'-',\n" +
                        "\t\t`t_wrt_customer`.`CITY_NAME`,\n" +
                        "\t\t`t_wrt_customer`.`COUNTY_NAME`\n" +
                        "\t) AS `customerlocation`,\n" +
                        "\t`t_wrt_customer`.`BUY_DATE` AS `buyDate`,\n" +
                        "\t`t_wrt_customer`.`BUY_TYPE` AS `buyType`,\n" +
                        "\t`t_wrt_customer`.`INTENTION_MODEL` AS `intentionModel`,\n" +
                        "\t`t_wrt_customer`.`INTENTION_FLAG` AS `intentionFlag`,\n" +
                        "\t`t_wrt_customer`.`THROW_REASON` AS `throwReason`,\n" +
                        "\t`t_wrt_customer`.`INTENTION_TIME` AS `intentionTime`,\n" +
                        "\t`t_wrt_customer`.`OTHER_CONDITION` AS `otherCondition`,\n" +
                        "\t`t_wrt_customer`.`CHANCE` AS `chance`,\n" +
                        "\t`t_wrt_customer`.`BUSINESS_DATE` AS `businessDate`,\n" +
                        "\t`t_wrt_customer`.`WECHAT` AS `wechat`,\n" +
                        "\t`t_wrt_customer`.`BIRTHDAY` AS `birthday`,\n" +
                        "\t`t_wrt_customer`.`EXISTING_DEVICE` AS `existingDevice`\n" +
                        "FROM\n" +
                        "\tt_wrt_customer\n" +
                        "LEFT JOIN t_sys_user ON t_sys_user.ID = t_wrt_customer.CREATE_USER_ID\n" +
                        "AND t_sys_user.JOB_STATE <> 1\n" +
                        "LEFT JOIN t_sys_role ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                        "AND t_sys_role.DEL_FLAG <> 1\n" +
                        "LEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                        "AND t_sys_depart.DEL_FLAG <> 1\n" +
                        "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                        "AND t_sys_company.DEL_FLAG <> 1\n" +
                        "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = t_sys_role.ROLE_NAME_ID\n" +
                        "WHERE 1 = 1\n" +
                        "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                        "AND t_sys_role_name.`NAME` = '区域经理'\n" +
                        "AND `t_wrt_customer`.`CREATE_USER_ID` IS NOT NULL\n" +
                        "AND `t_wrt_customer`.`NAME` IS NOT NULL\n" +
                        "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                        "AND t_wrt_customer.INTENTION_MODEL NOT IN ('无', '暂无')\n" +
                        "AND ( t_sys_user.ID = '"+userId+"' OR t_sys_user.PARENT_ID ='"+userId+"') \n" +
                        condition+
                        " GROUP BY\n" +
                        "\tt_sys_user.ID,\n" +
                        "\tt_wrt_customer.`ID`\n" +
                        "ORDER BY\n" +
                        "\tt_sys_company.SORT_NO,\n" +
                        "t_sys_user.ID,\n"+
                        "\tt_wrt_customer.CREATE_TIME DESC ";
            } else {
                //区域经理
                sqlCustomer = "select " +
                        "t_wrt_customer.`NAME` AS  name,\n" +
                        "t_wrt_customer.`ID` AS  id,\n" +
                        "t_wrt_customer.TEL AS tel,\n" +
                        "t_wrt_customer.SOURCE AS source,\n" +
                        "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                        "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                        "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                        "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                        "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                        "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                        "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                        "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                        " from t_wrt_customer where 1=1" + "\n" +
                        "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                        "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                        "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                        "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                        "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                        "AND CREATE_USER_ID=" + "'" + userId + "'" + "\n" +
                        condition
                        + "GROUP BY \n" +
                        "t_wrt_customer.`NAME`,\n" +
                        "t_wrt_customer.TEL,\n" +
                        "t_wrt_customer.SOURCE ,\n" +
                        "t_wrt_customer.INTENTION_MODEL ,\n" +
                        "t_wrt_customer.BUY_DATE ,\n" +
                        "t_wrt_customer.BUY_TYPE ,\n" +
                        "t_wrt_customer.EXISTING_DEVICE,\n" +
                        "t_wrt_customer.FUND_CONDITION ,\n" +
                        "t_wrt_customer.OTHER_CONDITION " + "\n"
                        + " order by t_wrt_customer.CREATE_TIME " + "\n";
            }
        } else {
            //其他岗位
            sqlCustomer = " SELECT\n" +
                    "\tt_sys_user.ID AS createUserId,\n" +
                    "\tt_sys_user.`NAME` AS createUserName,\n" +
                    "\tt_wrt_customer.`NAME` AS customerName,\n" +
                    "\tt_wrt_customer.`ID` AS customerId,\n" +
                    "\tt_wrt_customer.TEL AS tel,\n" +
                    "\tt_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                    "\tt_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                    "\tt_wrt_customer.PROVINCE_NAME AS provinceName,\n" +
                    "\tt_wrt_customer.CITY_NAME AS cityName,\n" +
                    "\tt_wrt_customer.COUNTY_NAME AS countyName,\n" +
                    "\tconcat_ws(\n" +
                    "\t\t'-',\n" +
                    "\t\t`t_wrt_customer`.`CITY_NAME`,\n" +
                    "\t\t`t_wrt_customer`.`COUNTY_NAME`\n" +
                    "\t) AS `customerlocation`,\n" +
                    "\t`t_wrt_customer`.`BUY_DATE` AS `buyDate`,\n" +
                    "\t`t_wrt_customer`.`BUY_TYPE` AS `buyType`,\n" +
                    "\t`t_wrt_customer`.`INTENTION_MODEL` AS `intentionModel`,\n" +
                    "\t`t_wrt_customer`.`INTENTION_FLAG` AS `intentionFlag`,\n" +
                    "\t`t_wrt_customer`.`THROW_REASON` AS `throwReason`,\n" +
                    "\t`t_wrt_customer`.`INTENTION_TIME` AS `intentionTime`,\n" +
                    "\t`t_wrt_customer`.`OTHER_CONDITION` AS `otherCondition`,\n" +
                    "\t`t_wrt_customer`.`CHANCE` AS `chance`,\n" +
                    "\t`t_wrt_customer`.`BUSINESS_DATE` AS `businessDate`,\n" +
                    "\t`t_wrt_customer`.`WECHAT` AS `wechat`,\n" +
                    "\t`t_wrt_customer`.`BIRTHDAY` AS `birthday`,\n" +
                    "\t`t_wrt_customer`.`EXISTING_DEVICE` AS `existingDevice`\n" +
                    "FROM\n" +
                    "\tt_wrt_customer\n" +
                    "LEFT JOIN t_sys_user ON t_sys_user.ID = t_wrt_customer.CREATE_USER_ID\n" +
                    "AND t_sys_user.JOB_STATE <> 1\n" +
                    "LEFT JOIN t_sys_role ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                    "AND t_sys_role.DEL_FLAG <> 1\n" +
                    "LEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                    "AND t_sys_depart.DEL_FLAG <> 1\n" +
                    "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                    "AND t_sys_company.DEL_FLAG <> 1\n" +
                    "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = t_sys_role.ROLE_NAME_ID\n" +
                    "WHERE\n" +
                    "\t1 = 1\n" +
                    "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                    "AND t_sys_role_name.`NAME` = '区域经理'\n" +
                    "AND `t_wrt_customer`.`CREATE_USER_ID` IS NOT NULL\n" +
                    "AND `t_wrt_customer`.`NAME` IS NOT NULL\n" +
                    "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                    "AND t_wrt_customer.INTENTION_MODEL NOT IN ('无', '暂无')\n" +
                    "AND (\n" +
                    "\tt_sys_user.ID IN (\n" +
                    "\t\tSELECT\n" +
                    "\t\t\tUSER_ID\n" +
                    "\t\tFROM\n" +
                    "\t\t\tt_sys_user_2_region\n" +
                    "\t\tWHERE\n" +
                    "\t\t\tREGION_ID IN (\n" +
                    "\t\t\t\tSELECT\n" +
                    "\t\t\t\t\tREGION_ID\n" +
                    "\t\t\t\tFROM\n" +
                    "\t\t\t\t\tt_sys_user_2_region\n" +
                    "\t\t\t\tWHERE\n" +
                    "\t\t\t\t\tUSER_ID = '"+userId+"'\n" +
                    "\t\t\t)\n" +
                    "\t)\n" +
                    "\tAND t_sys_role_name.FRONT_FLAG = 1\n" +
                    ")\n" +condition+
                    "GROUP BY\n" +
                    "\tt_sys_user.ID,\n" +
                    "\tt_wrt_customer.`ID`\n" +
                    "ORDER BY\n" +
                    "\tt_sys_company.SORT_NO,\n" +
                    "t_sys_user.ID,\n"+
                    "\tt_wrt_customer.CREATE_TIME DESC";
        }
        StringBuffer sqlBuffer = new StringBuffer(sqlCustomer);
        super.appendPageSql(sqlBuffer, Integer.valueOf(requestPage), Integer.valueOf(pageSize));
        List<JSONObject> records = super.queryForJsonList(sqlBuffer.toString());
        return records;
    }


    /*获取查询数目*/
    public Integer getCountByCompanyIdAndUserIdAndKey(String companyId, String userId, String condition, JSONObject roleFlag) {
         /*获得查询数目*/
        int recordCount = 0;
        String sqlCustomer = "";
        if (roleFlag.getString("manageFlag").equals("1")) {
            String all = "SELECT\n" +
                    "\tCOUNT(1) AS count\n" +
                    "FROM\n" +
                    "\tt_wrt_customer\n" +
                    "LEFT JOIN t_sys_user ON t_sys_user.ID = t_wrt_customer.CREATE_USER_ID\n" +
                    "AND t_sys_user.JOB_STATE <> 1\n" +
                    "LEFT JOIN t_sys_role ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                    "AND t_sys_role.DEL_FLAG <> 1\n" +
                    "LEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                    "AND t_sys_depart.DEL_FLAG <> 1\n" +
                    "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                    "AND t_sys_company.DEL_FLAG <> 1\n" +
                    "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = t_sys_role.ROLE_NAME_ID\n" +
                    "WHERE\n" +
                    "\t1 = 1\n" +
                    "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                    "AND t_sys_role_name.`NAME` = '区域经理'\n" +
                    "AND `t_wrt_customer`.`CREATE_USER_ID` IS NOT NULL\n" +
                    "AND `t_wrt_customer`.`NAME` IS NOT NULL\n" +
                    "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                    "AND t_wrt_customer.INTENTION_MODEL NOT in ('无', '暂无')\n" +
                    "AND t_sys_company.ID IN (\n"+companyId+")\n" + condition;
            JSONObject objCustomer = super.queryForJsonObject(all, null);
            recordCount = objCustomer.getInteger("count");
        } else {
            if (roleFlag.getString("frontFlag").equals("1")) {
                if (roleFlag.getString("areaType").equals("1")) {
                    //大区经理
                    sqlCustomer = "SELECT\n" +
                            "COUNT(1) AS count\n" +
                            "\n" +
                            "FROM\n" +
                            "\tt_wrt_customer\n" +
                            "LEFT JOIN t_sys_user ON t_sys_user.ID = t_wrt_customer.CREATE_USER_ID\n" +
                            "AND t_sys_user.JOB_STATE <> 1\n" +
                            "LEFT JOIN t_sys_role ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                            "AND t_sys_role.DEL_FLAG <> 1\n" +
                            "LEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                            "AND t_sys_depart.DEL_FLAG <> 1\n" +
                            "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                            "AND t_sys_company.DEL_FLAG <> 1\n" +
                            "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = t_sys_role.ROLE_NAME_ID\n" +
                            "WHERE 1 = 1\n" +
                            "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                            "AND t_sys_role_name.`NAME` = '区域经理'\n" +
                            "AND `t_wrt_customer`.`CREATE_USER_ID` IS NOT NULL\n" +
                            "AND `t_wrt_customer`.`NAME` IS NOT NULL\n" +
                            "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                            "AND t_wrt_customer.INTENTION_MODEL NOT IN ('无', '暂无')\n" +
                            condition+
                            "AND ( t_sys_user.ID = '"+userId+"' OR t_sys_user.PARENT_ID ='"+userId+"')";
                } else {
                    //区域经理
                    sqlCustomer = "SELECT COUNT(1) AS  count\n" +
                            "from (\n" +
                            "select " +
                            "t_wrt_customer.`NAME` AS  name,\n" +
                            "t_wrt_customer.`ID` AS  id,\n" +
                            "t_wrt_customer.TEL AS tel,\n" +
                            "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                            "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                            "t_wrt_customer.SOURCE AS source,\n" +
                            "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                            "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                            "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                            "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                            "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                            "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                            " from t_wrt_customer where 1=1" + "\n" +
                            "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                            "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                            "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                            "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                            "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                            "AND CREATE_USER_ID=" + "'" + userId + "'" + "\n" +
                            condition
                            + "GROUP BY \n" +
                            "t_wrt_customer.`NAME`,\n" +
                            "t_wrt_customer.TEL,\n" +
                            "t_wrt_customer.SOURCE ,\n" +
                            "t_wrt_customer.INTENTION_MODEL ,\n" +
                            "t_wrt_customer.BUY_DATE ,\n" +
                            "t_wrt_customer.BUY_TYPE ,\n" +
                            "t_wrt_customer.EXISTING_DEVICE,\n" +
                            "t_wrt_customer.FUND_CONDITION ,\n" +
                            "t_wrt_customer.OTHER_CONDITION " + "\n"
                            + " order by t_wrt_customer.CREATE_TIME\n " + ") AS sys";
                }
            } else {
                //其他岗位
                sqlCustomer = "SELECT\n" +
                        "COUNT(1) AS count\n" +
                        "FROM\n" +
                        "\tt_wrt_customer\n" +
                        "LEFT JOIN t_sys_user ON t_sys_user.ID = t_wrt_customer.CREATE_USER_ID\n" +
                        "AND t_sys_user.JOB_STATE <> 1\n" +
                        "LEFT JOIN t_sys_role ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                        "AND t_sys_role.DEL_FLAG <> 1\n" +
                        "LEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                        "AND t_sys_depart.DEL_FLAG <> 1\n" +
                        "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                        "AND t_sys_company.DEL_FLAG <> 1\n" +
                        "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = t_sys_role.ROLE_NAME_ID\n" +
                        "WHERE\n" +
                        "\t1 = 1\n" +
                        "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                        "AND t_sys_role_name.`NAME` = '区域经理'\n" +
                        "AND `t_wrt_customer`.`CREATE_USER_ID` IS NOT NULL\n" +
                        "AND `t_wrt_customer`.`NAME` IS NOT NULL\n" +
                        "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                        "AND t_wrt_customer.INTENTION_MODEL NOT IN ('无', '暂无')\n" +
                        "AND (\n" +
                        "\tt_sys_user.ID IN (\n" +
                        "\t\tSELECT\n" +
                        "\t\t\tUSER_ID\n" +
                        "\t\tFROM\n" +
                        "\t\t\tt_sys_user_2_region\n" +
                        "\t\tWHERE\n" +
                        "\t\t\tREGION_ID IN (\n" +
                        "\t\t\t\tSELECT\n" +
                        "\t\t\t\t\tREGION_ID\n" +
                        "\t\t\t\tFROM\n" +
                        "\t\t\t\t\tt_sys_user_2_region\n" +
                        "\t\t\t\tWHERE\n" +
                        "\t\t\t\t\tUSER_ID = '"+userId+"'\n" +
                        "\t\t\t)\n" +
                        "\t)\n" +
                        "\tAND t_sys_role_name.FRONT_FLAG = 1\n" +
                        ")\n";
            }
            String count = super.queryForString(sqlCustomer.toString());
            recordCount = Integer.parseInt(count);
        }
        return recordCount;
    }

    /*集团下部门*/
    public List<JSONObject> getForDepartByCompanyId(String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_depart.`NAME` AS departName,\n" +
                "t_sys_role_name.`NAME` AS roleName,\n" +
                "t_sys_user.`NAME` AS userName,\n" +
                "t_sys_user.`ID` AS userId,\n" +
                "COUNT(t_wrt_customer.ID) AS num,\n" +
                "COUNT(case when (t_wrt_customer.BUY_DATE='一周内'  OR t_wrt_customer.BUY_DATE='一周' )then t_wrt_customer.BUY_DATE end ) AS weekNum,\n" +
                "COUNT(case when (t_wrt_customer.BUY_DATE='一月内' OR t_wrt_customer.BUY_DATE='本月') then t_wrt_customer.BUY_DATE end ) AS MonNum,\n" +
                "COUNT(case when t_wrt_customer.BUY_DATE='一月以上' then t_wrt_customer.BUY_DATE end ) AS MoreNum,\n" +
                "t_sys_region.`NAME` AS regionName,\n" +
                "t_sys_region.ID AS regionId\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID AND t_sys_user.JOB_STATE<>1\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID AND t_sys_role.DEL_FLAG<>1\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID AND t_sys_depart.DEL_FLAG<>1\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID AND t_sys_company.DEL_FLAG<>1\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID AND t_sys_role_name.DEL_FLAG<>1\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=t_sys_user.ID \n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID AND t_sys_region.DEL_FLAG<>1\n" +
                "WHERE 1=1\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_sys_role_name.`NAME`='区域经理'\n" +
                "AND `t_wrt_customer`.`CREATE_USER_ID` IS NOT NULL\n" +
                "AND `t_wrt_customer`.`NAME` IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in('无','暂无')\n" +
                "AND t_sys_company.ID='"+companyId+"'\n" +
                "AND t_sys_region.PARENT_ID<>0\n" +
                "GROUP by t_sys_depart.ID,t_sys_user.ID\n" +
                "ORDER BY t_sys_depart.SORT_NO,num");
        return super.queryForJsonList(sql.toString());
    }

    /*个人*/
    public List<JSONObject> getForPersonalIntentCustomerList( String condition, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_rpt_thursday_genzong.ID AS id,\n" +
                "t_rpt_thursday_genzong.REGIONAL_MANAGER_ID AS regionalManagerId,\n" +
                "t_rpt_thursday_genzong.REGIONAL_MANAGER_NAME AS regionalMangerName,\n" +
                "t_rpt_thursday_genzong.CUSTOMER_ID AS customerId,\n" +
                "t_rpt_thursday_genzong.CUSTOMER_NAME AS customerName,\n" +
                "t_rpt_thursday_genzong.CUSTOMER_TEL AS customerTel,\n" +
                "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                "t_rpt_thursday_genzong.PROVINCE_NAME AS provinceName,\n" +
                "t_rpt_thursday_genzong.CITY_NAME AS cityName,\n" +
                "t_rpt_thursday_genzong.COUNTY_NAME AS countyName,\n" +
                "\tconcat_ws(\n" +
                "\t\t'-',\n" +
                "\t\t`t_wrt_customer`.`CITY_NAME`,\n" +
                "\t\t`t_wrt_customer`.`COUNTY_NAME`\n" +
                "\t) AS `customerlocation`,\n"+
                "t_rpt_thursday_genzong.CUSTOMER_ADDRESS AS customerAddress,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                "t_rpt_thursday_genzong.BUY_TYPE AS buyType,\n" +
                "t_rpt_thursday_genzong.NEED_MODEL_ID AS needModelId,\n" +
                "t_rpt_thursday_genzong.NEED_MODEL AS needModel,\n" +
                "t_rpt_thursday_genzong.CONFIRMATION_STATUS AS confirmationStatus,\n" +
                "t_rpt_thursday_genzong.LOSTORDER_REASON AS lostorderReason,\n" +
                "t_wrt_customer.INTENTION_FLAG AS intentionFlag,\n" +
                "t_rpt_thursday_genzong.TRANSACTION_PROBABILITY AS transactionProbability,-- 成交概率\n" +
                "t_wrt_customer.CHANCE AS chance,-- 本月成交概率\n" +
                "t_rpt_thursday_genzong.CONFIRMATION_DATE AS confirMationDay,\n" +
                "t_rpt_thursday_genzong.BUILD_CONDITION AS buildCondition,\n" +
                "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                "FROM\n" +
                "t_rpt_thursday_genzong\n" +
                "LEFT JOIN t_wrt_customer ON t_rpt_thursday_genzong.CUSTOMER_ID=t_wrt_customer.ID\n" +
                "WHERE 1=1\n" +
                condition +
                "GROUP BY t_rpt_thursday_genzong.COMPANY_ID,t_rpt_thursday_genzong.REGIONAL_MANAGER_ID,t_rpt_thursday_genzong.CUSTOMER_ID\n" +
                "ORDER BY t_rpt_thursday_genzong.COMPANY_ID,t_rpt_thursday_genzong.CREATE_TIME\n" +
                "\n" +
                "\n");
        super.appendPageSql(sql, requestPage, pageSize);
        List<JSONObject> records = super.queryForJsonList(sql.toString());
        return records;
    }

    /*查询个人数量*/
    public Integer getCountPersonalIntentCustomerList(String userId, String condition) {
        StringBuffer sql = new StringBuffer();
        int recordCount = 0;
        sql.append(
                "SELECT\n" +
                        "COUNT(1)\n" +
                        "FROM\n" +
                        "t_rpt_thursday_genzong\n" +
                        "LEFT JOIN t_wrt_customer ON t_rpt_thursday_genzong.CUSTOMER_ID=t_wrt_customer.ID\n" +
                        "WHERE 1=1\n"
        );
        sql.append(condition);
        String count = super.queryForString(sql.toString());
        recordCount = Integer.parseInt(count);
        return recordCount;
    }


    public List<JSONObject> getForCompanyIntentCustomerList(String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_company.ID AS companyId,\n"+
                "t_sys_company.NAME AS companyName,\n"+
                "t_sys_depart.`NAME` AS departName,\n" +
                "t_sys_role_name.`NAME` AS roleName,\n" +
                "t_sys_user.`NAME` AS userName,\n" +
                "t_sys_user.`ID` AS userId,\n" +
                "COUNT(t_wrt_customer.ID) AS num,\n" +
                "COUNT(case when (t_wrt_customer.BUY_DATE='一周内'  OR t_wrt_customer.BUY_DATE='一周' )then t_wrt_customer.BUY_DATE end ) AS weekNum,\n" +
                "COUNT(case when (t_wrt_customer.BUY_DATE='一月内' OR t_wrt_customer.BUY_DATE='本月') then t_wrt_customer.BUY_DATE end ) AS MonNum,\n" +
                "COUNT(case when t_wrt_customer.BUY_DATE='一月以上' then t_wrt_customer.BUY_DATE end ) AS MoreNum,\n" +
                "t_sys_region.`NAME` AS regionName,\n" +
                "t_sys_region.ID AS regionId\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID AND t_sys_user.JOB_STATE<>1\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID AND t_sys_role.DEL_FLAG<>1\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID AND t_sys_depart.DEL_FLAG<>1\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID AND t_sys_company.DEL_FLAG<>1\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID AND t_sys_role_name.DEL_FLAG<>1\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=t_sys_user.ID \n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID AND t_sys_region.DEL_FLAG<>1\n" +
                "WHERE 1=1\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_sys_role_name.`NAME`='区域经理'\n" +
                "AND `t_wrt_customer`.`CREATE_USER_ID` IS NOT NULL\n" +
                "AND `t_wrt_customer`.`NAME` IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in('无','暂无')\n" +
                "AND t_sys_company.ID in("+companyId+")\n" +
                "AND t_sys_region.PARENT_ID<>0\n" +
                "GROUP by t_sys_company.ID,t_sys_depart.ID,t_sys_user.ID\n" +
                "ORDER BY t_sys_company.SORT_NO,t_sys_depart.SORT_NO,num");
        return super.queryForJsonList(sql.toString());
    }


    public List<JSONObject> getForDepartIntentCustomerList(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_rpt_thursday_genzong.BIG_REGIONAL_ID AS bigRegionalId,\n" +
                "t_sys_region.`NAME` AS bigRegionalName,\n" +
                "v_sys_user.roleName AS roleName,\n" +
                "t_rpt_thursday_genzong.REGIONAL_MANAGER_ID AS regionalManagerId,\n" +
                "t_rpt_thursday_genzong.REGIONAL_MANAGER_NAME AS regionalManagerName,\n" +
                "COUNT(case when (t_wrt_customer.BUY_DATE='一周内'  OR t_wrt_customer.BUY_DATE='一周' )then t_wrt_customer.BUY_DATE end ) AS weekNum,\n" +
                "COUNT(case when (t_wrt_customer.BUY_DATE='一月内' OR t_wrt_customer.BUY_DATE='本月') then t_wrt_customer.BUY_DATE end ) AS MonNum,\n" +
                "COUNT(case when t_wrt_customer.BUY_DATE='一月以上' then t_wrt_customer.BUY_DATE end ) AS MoreNum,\n" +
                " COUNT(t_rpt_thursday_genzong.ID) AS num,\n" +
                "t_rpt_thursday_genzong.REGIONAL_ID,\n" +
                "t_sys_region_2.`NAME` AS regionName\n" +
                "FROM\n" +
                "t_rpt_thursday_genzong\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID=t_rpt_thursday_genzong.CUSTOMER_ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_rpt_thursday_genzong.BIG_REGIONAL_ID\n" +
                "LEFT JOIN t_sys_region AS t_sys_region_2 ON t_sys_region_2.ID=t_rpt_thursday_genzong.REGIONAL_ID\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.id=t_rpt_thursday_genzong.REGIONAL_MANAGER_ID\n" +
                "WHERE 1=1\n" +
                "AND v_sys_user.jobState<>1\n" +
                "AND t_rpt_thursday_genzong.NEED_MODEL IS NOT NULL\n" +
                "AND t_rpt_thursday_genzong.CUSTOMER_NAME IS NOT NULL\n" +
                "AND (\n" +
                " v_sys_user.parentId = '"+userId+"'\n" +
                "OR (\n" +
                "\t\t\t\t\tv_sys_user.id in(\n" +
                "\t\t\t\t\t\t\tSELECT USER_ID\n" +
                "\t\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t\tt_sys_user_2_region \n" +
                "\t\t\t\t\t\t\t\t\t\t\tWHERE \n" +
                "\t\t\t\t\t\t\t\t\t\t\t\t\tREGION_ID in(\n" +
                "\t\t\t\t\t\t\tSELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = '"+userId+"'\n" +
                "\t)\n" +
                "\t\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\tAND v_sys_user.frontFlag= 1\n" +
                "\t\t\t\t)\n" +
                ")\n" +
                "GROUP BY t_rpt_thursday_genzong.REGIONAL_MANAGER_ID\n" +
                "ORDER BY  num\n");
        return super.queryForJsonList(sql.toString());
    }
}

